import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Scanner;

public class StudentManage {
    static Connection conn=null;
    //创建连接桥
    static PreparedStatement pstm=null;
    //预编译sql语句
    static ResultSet rs=null;
    //得到数据库查询结果集

    public static void main(String[] args) {
        getConnection();
       regist();
    }



    public static Connection getConnection(){
        try {
            Class.forName("com.mysql.jdbc.Driver");
            //加载驱动
            String url="jdbc:mysql://localhost:3306/studntinfo";
            //数据库查询地址及协议
            String name="root";
            String pwd="123456";
            conn= DriverManager.getConnection(url,name,pwd);
            //加载数据库管理资源
        }catch (Exception e){
            System.out.println(e);
        }
        System.out.println(conn);
        return  conn;
    }

    //学生成绩管理类
    private static void regist() {
        System.out.println("==========请选择您需要进行的操作：==========");
        System.out.println("    1.查看学生成绩列表");
        System.out.println("    2.查看平均分");
        System.out.println("    3.降序查看学生成绩");
        System.out.println("    4.学生成绩导出");
        Scanner sc1=new Scanner(System.in);
        int input = sc1.nextInt();
        switch (input){
            case 1:
                //查看学生成绩列表
                List<Stu> list=new ArrayList<Stu>();

                     String sql="select a.student_id,a.score,a.course_id,b.id,b.username from s_score a " +
                             "inner join s_student b on a.student_id=b.id ";
                try {
                    pstm=conn.prepareStatement(sql);
                    rs=pstm.executeQuery();
                    Stu stu=new Stu();
             while (rs.next()){
                 int i=1;
                        stu.setNo(rs.getInt("student_id"));
                        stu.setName(rs.getString("username"));
                        stu.setScore(rs.getInt("score"));
                        list.add(stu);
                        //由于刚开始学，目前只能做到查询关联两个表（班级、科目无法实现）
                 for (Stu stuList:list){
                     if (i!=2){
                         System.out.println("学号："+stuList.getNo()+ "  姓名:"+stuList.getName()+
                                 "  成绩："+stuList.getScore());
                         i++;
                     }else{
                         continue;
                     }
                 }
                    }

                }catch (SQLException e){
                    System.out.println(e);
                }finally {
                    if (rs!=null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }else if (pstm!=null){
                        try {
                            pstm.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }else if (conn!=null){
                        try {
                            conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
                break;
            case 2:
                //查看平均分
                List<Stu> list2=new ArrayList<Stu>();
                int sum=0;
                String sql2="select a.student_id,a.score,a.course_id,b.id,b.username from s_score a " +
                        "inner join s_student b on a.student_id=b.id ";
                try {
                    pstm=conn.prepareStatement(sql2);
                    rs=pstm.executeQuery();
                    Stu stu=new Stu();
                    while (rs.next()){
                        int i=1;
                        stu.setNo(rs.getInt("student_id"));
                        stu.setName(rs.getString("username"));
                        stu.setScore(rs.getInt("score"));
                        list2.add(stu);
                        //由于刚开始学，目前只能做到查询关联两个表（班级、科目无法实现）
                        for (Stu stuList:list2){
                            if (i!=2){
                            System.out.println("学号："+stuList.getNo()+ "  姓名:"+stuList.getName()+
                                    "  成绩："+stuList.getScore());
                            sum=stuList.getScore()+sum;
                            i++;
                            }else{
                                continue;
                            }
                        }
                    }
                    System.out.println("================");
                    System.out.println("平均分为：【"+sum/2+"】");
                }catch (SQLException e){
                    System.out.println(e);
                }finally {
                    if (rs!=null){
                        try {
                            rs.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }else if (pstm!=null){
                        try {
                            pstm.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }else if (conn!=null){
                        try {
                            conn.close();
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    }
                }
                break;
            case 3:
                //降序查看学生成绩
                int i=0;
                Stu stu=new Stu();
                List<Stu> list3=new ArrayList<Stu>();
                String sql3="select a.student_id,a.score,a.course_id,b.id,b.username from s_score a " +
                        "inner join s_student b on a.student_id=b.id  order by score desc";
                try {
                    pstm=conn.prepareStatement(sql3);

                    rs=pstm.executeQuery();
                    while (rs.next()){
                        stu.setNo(rs.getInt("student_id"));
                        stu.setName(rs.getString("course_id"));
                        stu.setScore(rs.getInt("score"));
                        list3.add(stu);
                    }
                    System.out.println("======成绩降序排序显示：======");
                    for (Stu stuList3:list3){

                            System.out.println("学号："+stuList3.getNo()+" 学科编号："+stuList3.getName()
                                    +" 成绩："+stuList3.getScore());
                            i++;

                        }
                }catch (Exception e){
                    System.out.println(e);
                }
                break;
            case 4:
                //学生成绩导出
                break;
            default:
                System.out.println("您的输入不合法！！！");
                break;
        }

    }
}
